package dao;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jdbc.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ExportExlDaoImp implements  ExportExlDao{


    public JSONArray ExportExlDaoOrIntList(JSONArray ids) throws SQLException {

        String sql="SELECT a.id,b.teacherName,a.dmy,a.classHours,c.className,a.courseName,if(a.mark=0,'已记','未记') as mark FROM schooltreasure.curriculum as a\n" +
                " inner join schooltreasure.teacher as b on a.teacherId=b.id\n" +
                "  inner join schooltreasure.classtable as c on a.classID=c.classId\n" +
                "  where in"+ JSON.toJSON(ids).toString().replace("[","(").replace("]",")");

        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet res=preparedStatement.executeQuery();//执行sql语句，无论什么语句都能执行
        JSONArray jSONArray=new JSONArray();
        while (res.next()){
            JSONObject jSONObject=new JSONObject();
            jSONObject.put("id",res.getInt("id"));
            jSONObject.put("teacherName",res.getString("teacherName"));
            jSONObject.put("dym",res.getString("dym"));
            jSONObject.put("classHours",res.getString("classHours"));
            jSONObject.put("className",res.getString("className"));
            jSONObject.put("courseName",res.getString("courseName"));
            jSONObject.put("mark",res.getString("mark"));
            jSONArray.add(jSONObject);
        }

        jdbc.guanbi(res, preparedStatement, connection);
        return jSONArray;
    }


    public JSONArray ExportExlDaoOrNameOrBanjiOrDateTime(String name, String Banji, String startDate, String endDate,int mark) throws SQLException {
      String sql="SELECT a.id,b.teacherName,a.dmy,a.classHours,c.className,a.courseName,if(a.mark=0,'已记','未记') as mark FROM schooltreasure.curriculum as a\n" +
              " inner join schooltreasure.teacher as b on a.teacherId=b.id\n" +
              "  inner join schooltreasure.classtable as c on a.classID=c.classId\n" +
              "  where b.teacherName='"+name+"' and c.className='"+Banji+"' and a.mark="+mark+" and a.dmy>='"+startDate+"' and a.dmy<='"+endDate+"'";
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet res=preparedStatement.executeQuery();//执行sql语句，无论什么语句都能执行
        JSONArray jSONArray=new JSONArray();
        while (res.next()){
            JSONObject jSONObject=new JSONObject();
            jSONObject.put("id",res.getInt("id"));
            jSONObject.put("teacherName",res.getString("teacherName"));
            jSONObject.put("dym",res.getString("dym"));
            jSONObject.put("classHours",res.getString("classHours"));
            jSONObject.put("className",res.getString("className"));
            jSONObject.put("courseName",res.getString("courseName"));
            jSONObject.put("mark",res.getString("mark"));
            jSONArray.add(jSONObject);
        }
        jdbc.guanbi(res, preparedStatement, connection);
        return jSONArray;
    }
}
